go

exec dbo.p_drop_proc_if_exists 'meta_add_index';

go

--Function of adding new index
create procedure meta_add_index(
    --Index id
    @a_index_id binary(16),
    --Table id
    @a_table_id binary(16),
    --Is unique
    @a_is_unique numeric(1),
    --Column id list
    @a_column_id_list varbinary(256))
with execute as owner
as  
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Column list
    @a_column_list varchar(max),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Current index
    @a_curr_index int,
    --Current position
    @a_curr_position int,
    --Item length
    @a_item_length int,
    --Row count
    @a_row_count numeric,
    --Index id str
    @a_index_id_str varchar(32);
    
    set @a_curr_index = 1;
    set @a_curr_position = 1;
    set @a_item_length = 16;
    set @a_column_list = '';
    set @a_index_id_str = dbo.p_bin_to_str(@a_index_id);

    --If there is no column specified
    if @a_column_id_list is null
        return;

    --Searching for the index with the same id
    select @a_row_count = count(*)
    from meta_indexes
    where index_id = @a_index_id;
    
    if @a_row_count > 0
    begin
        raiserror('A index with the specified id was already added (index_id = %s) (#error_code = 20028)',
        16, 1, @a_index_id_str);
        return;
    end;
    
    --Searching for the index with the same column set
    select @a_row_count = count(*)
    from meta_indexes
    where column_id_list = @a_column_id_list;
    
    if @a_row_count > 0
    begin
        raiserror('A index with the specified column set was already added (#error_code = 20029)',
        16, 1);
        return;
    end;
    
    --Reading table name
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Reading user columns
    while datalength(@a_column_id_list) - @a_curr_position + 1 >= 16 
    begin
		
		set @a_column_id = substring(@a_column_id_list,
			@a_curr_position, @a_item_length);
		    
        set @a_curr_index = @a_curr_index + 1;
        set @a_curr_position = @a_curr_position + 16;
            
        --Reading column name
        select @a_column_name = column_name
        from meta_table_columns
        where table_id = @a_table_id
        and column_id = @a_column_id;
            
        if len(@a_column_list) > 0
            set @a_column_list = @a_column_list + ',';
            
        set @a_column_list = @a_column_list + @a_column_name;        
    end;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    insert into meta_indexes(index_id, table_id, is_unique, column_id_list)
    values (@a_index_id, @a_table_id, @a_is_unique, @a_column_id_list);
    
    --Creating the index
    exec ('create index i'
        + @a_index_id_str
        + ' on tbl_'  + @a_table_name
        + ' (' + @a_column_list + ')');
    
    if @a_is_unique = 1
		exec dbo.p_recreate_index_triggers @a_index_id;
        
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_recreate_index_triggers';

go

--Function that recreates the index triggers
create procedure p_recreate_index_triggers(
    --Index id
    @a_index_id binary(16))
as
begin
	declare
	--Index uniquenss
	@a_is_unique numeric,
	--Table id
	@a_table_id binary(16),
	--Column id list
	@a_column_id_list varbinary(256),
	--Table name
    @a_table_name varchar(26),
    --Column list
    @a_column_list varchar(max),
    --Column condition list
    @a_column_condition varchar(max),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(26),
    --Current index
    @a_curr_index int,
    --Current position
    @a_curr_position int,
    --Item length
    @a_item_length int,
    --Row count
    @a_row_count numeric,
    --Index id str
    @a_index_id_str varchar(32);
    
    set @a_curr_index = 1;
    set @a_curr_position = 1;
    set @a_item_length = 16;
    set @a_column_list = 'update(is_active)';
    set @a_column_condition = '';
    set @a_index_id_str = dbo.p_bin_to_str(@a_index_id);
    
    --Reading index properties
    select @a_table_id = table_id,
		@a_is_unique = is_unique,
		@a_column_id_list = column_id_list 
    from meta_indexes
    where index_id = @a_index_id;
    
    --Reading table name
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --If the index is unique
    if @a_is_unique = 1
    begin
        --Reading user columns
		while datalength(@a_column_id_list) - @a_curr_position + 1 >= 16 
		begin
			
			set @a_column_id = substring(@a_column_id_list,
				@a_curr_position, @a_item_length);
			    
			set @a_curr_index = @a_curr_index + 1;
			set @a_curr_position = @a_curr_position + 16;
	            
			--Reading column name
			select @a_column_name = column_name
			from meta_table_columns
			where table_id = @a_table_id
			and column_id = @a_column_id;
	            
			if len(@a_column_list) > 0
				set @a_column_list = @a_column_list + ' or ';
			if len(@a_column_condition) > 0
				set @a_column_condition = @a_column_condition + ' and ';
	            
			set @a_column_list = @a_column_list + 'update(' + @a_column_name + ')';
			set @a_column_condition = @a_column_condition + 's.' 
            + @a_column_name + '=t.' + @a_column_name;        
		end;
		
		--Dropping the trigger
		exec ('if object_id(''q' + @a_index_id_str + ''',''TR'') is not null'
		+ ' drop trigger q' + @a_index_id_str + ';');
    
        --Creating the trigger that enforces uniqueness
        exec ('create trigger q' + @a_index_id_str
        + ' on tbl_' + @a_table_name
        + ' after insert, update'        
        + ' as begin'
        + '   declare'
        + '   @a_recycle_bin numeric,'
        + '   @a_row_count numeric,'
        + '   @a_inserted_count numeric;'
              --Checking the target columns
        + '   if not (' + @a_column_list + ') return;'       
              --Checking the recycle bin availability
        + '   select @a_recycle_bin = int_value from meta_options where option_name = ''RECYCLE_BIN'';'
              --If the recycle bin is enabled
        + '   if @a_recycle_bin = 1'
                   --Searching for another row with the same column values in active rows
        + '        select @a_row_count = count(*) from inserted i, tbl_'
        +          @a_table_name + ' s, tbl_' + @a_table_name + ' t'
        + '        where t.id=i.id and s.is_active = 1 and ' + @a_column_condition + ';'
		      --If the recycle bin is not enabled
        + '   else'
                   --Searching for another row with the same column values in all rows
        + '        select @a_row_count = count(*) from inserted i, tbl_'
        +          @a_table_name + ' s, tbl_' + @a_table_name + ' t'
        + '        where t.id=i.id and ' + @a_column_condition + ';' 
        + '   select @a_inserted_count = count(*) from inserted;'       
        + '   if @a_row_count > @a_inserted_count'
        + '   begin'
        + '     raiserror(''The operation results in breaking the column values uniqueness (#error_code = 20055)'', 16, 1);'
        + '     rollback;'
        + '   end;'
        + ' end;');
    end;
end;

go

exec dbo.p_drop_proc_if_exists 'p_recreate_col_index_triggers';

go

--Function that recreates the column index triggers
create procedure p_recreate_col_index_triggers(
    --Column id
    @a_column_id binary(16))
as
begin
	declare
	--Table id
    @a_table_id binary(16);

    --Reading column properties
    select @a_table_id = table_id
    from meta_table_columns
    where column_id = @a_column_id;
    
    --Index id
    declare @a_index_id binary(16),
    --Index cursor
    @a_index_cursor cursor;
    
    set @a_index_cursor = cursor fast_forward read_only for
    select index_id
    from meta_indexes
    where table_id = @a_table_id
    and is_unique = 1
    and dbo.p_column_is_indexed(index_id, @a_column_id) = 1;
    
    open @a_index_cursor;
    fetch next from @a_index_cursor into @a_index_id;
    
    while @@fetch_status = 0
    begin
		exec dbo.p_recreate_index_triggers @a_index_id;
		fetch next from @a_index_cursor into @a_index_id;
    end;
    
    close @a_index_cursor;
    deallocate @a_index_cursor;
end;

go

exec dbo.p_drop_func_if_exists 'p_column_is_indexed';

go

--Function of checking if column is indexed
create function p_column_is_indexed(
    --Index id
    @a_index_id binary(16),
    --Column id
    @a_column_id binary(16))
returns numeric
as
begin
	declare
	--Index column id list
    @a_column_id_list varbinary(max),
    --Position
    @a_position numeric,
    --Result
    @a_result numeric;
    
    --Reading column id list
    select @a_column_id_list = column_id_list
    from meta_indexes
    where index_id = @a_index_id;
    
    set @a_position = charindex(@a_column_id, @a_column_id_list);
    
    if @a_position = 0
        set @a_result = 0;
    else if (@a_position - 1) % 16 = 0
        set @a_result = 1;
    else
        set @a_result = 0;
        
    return @a_result;
end;

go

exec dbo.p_drop_proc_if_exists 'p_delete_column_indexes';

go

--Function of deleting all indexes that include the column
create procedure p_delete_column_indexes(
    --Column id
    @a_column_id binary(16),
    --Saving the index list
    @a_save_index_list numeric(1) = 0)
as  
begin
	declare
	--Table id
    @a_table_id binary(16);
    
    --Reading column properties
    select @a_table_id = table_id
    from meta_table_columns
    where column_id = @a_column_id;
    
    --If there is a need to save the index list
    if @a_save_index_list = 1
    begin
        --If the column index table exists
		if OBJECT_ID('tempdb..##column_indexes', 'U') is not null
		begin
			truncate table ##column_indexes;
			
			insert into ##column_indexes
			select index_id, is_unique, column_id_list
			from meta_indexes
			where table_id = @a_table_id
			and dbo.p_column_is_indexed(index_id, @a_column_id) = 1;
		end
		else
			select index_id, is_unique, column_id_list into ##column_indexes
			from meta_indexes
			where table_id = @a_table_id
			and dbo.p_column_is_indexed(index_id, @a_column_id) = 1;
    end;
    
    --Index id
    declare @a_index_id binary(16),
    --Index cursor
    @a_index_cursor cursor;
    
    set @a_index_cursor = cursor fast_forward read_only for
    select index_id
    from meta_indexes
    where table_id = @a_table_id
		and dbo.p_column_is_indexed(index_id, @a_column_id) = 1;
    
    open @a_index_cursor;
    fetch next from @a_index_cursor into @a_index_id;
    
    while @@fetch_status = 0
    begin
		exec dbo.meta_delete_index @a_index_id;
		fetch next from @a_index_cursor into @a_index_id;
    end;
    
    close @a_index_cursor;
    deallocate @a_index_cursor;    
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_index';

go

--Function of dropping table index
create procedure meta_delete_index(
    --Index id
    @a_index_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
	@a_table_id binary(16),
	--Table name
	@a_table_name varchar(26),
	--Row count
    @a_row_count numeric,
    --Index uniqueness
    @a_is_unique numeric,
    --Index id str
    @a_index_id_str varchar(32);
    
    set @a_index_id_str = dbo.p_bin_to_str(@a_index_id);
    
    --Searching for the index with the same id
    select @a_row_count = count(*)
    from meta_indexes
    where index_id = @a_index_id;
    
    if @a_row_count = 0
    begin
        raiserror('A index with the specified id was not found (index_id = %s) (#error_code = 20030)',
        16, 1, @a_index_id_str);
        return;
    end;
    
    --Reading table id
    select @a_table_id = table_id, @a_is_unique = is_unique
    from meta_indexes
    where index_id = @a_index_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;

    --Updating the metadata
    delete from meta_indexes
    where index_id = @a_index_id;
    
    exec ('drop index tbl_' + @a_table_name + '.i'
    + @a_index_id_str);
    
    --If the index is unique
    if @a_is_unique = 1
        --Dropping the trigger
        exec ('drop trigger q' + @a_index_id_str);
    
    commit;
end;